package com.xpc.mapper;

import com.xpc.pojo.qin.ClassPositionSimpleInfo;
import com.xpc.pojo.qin.ClassPositionSimpleInfo_Member;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Qinkuer
 * @version 1.0.0
 * @ProjectName UniversityGradeAdministrationSystem
 * @ClassName ClassPositionSimpleInfoMapper.java
 * @Description 获取关于班级班干的信息
 * @createTime 2022年05月26日 10:20:00
 */
@Mapper
public interface ClassPositionSimpleInfoMapper {

    /**
        * @description 获取所有班级班干粗略信息
        * @param
        * @return  java.util.List<com.xpc.pojo.qin.ClassPositionSimpleInfo>
        * @author Qinkuer
        * @Date  2022/5/26
        */
    @Select("        select c.id as classId,c.name as className,c.studentNums,c.major_id as majorId,m.name as majorName from\n" +
            "            (select c.*,count(s.user_id) as studentNums from class c left join student s on c.id =s.class_id group by c.id ) as c\n" +
            "        join major m\n" +
            "        where c.major_id=m.id  order by classId ")
    List<ClassPositionSimpleInfo> getList();
    //获取某个班级的班长何和团支书信息
    @Select("        select mcc.committeeId,mcc.class_id as classId,mcc.user_id as userId,mcc.position_id as positionId,mcc.positionName,u.name from\n" +
            "            (select mcc.id as committeeId,mcc.class_id,mcc.user_id,mcc.position_id,p.name as positionName from\n" +
            "                member_committee_class mcc\n" +
            "                    join `position` p\n" +
            "             where mcc.position_id = p.id and mcc.position_id in (1,3) ) as mcc\n" +
            "                left join `user` u on mcc.user_id =u.user_id")
    List<ClassPositionSimpleInfo_Member> getMLs();

}
